<?php
class SloggyPosts extends Slog_Db_Table 
{
	public function __create()
	{
		$this->query(<<<EOD
		CREATE TABLE `sloggy_posts` (
		  `id` int(10) unsigned NOT NULL auto_increment,
		  `id_user` int(10) unsigned NOT NULL,
		  `title` varchar(255) NOT NULL,
		  `body` longtext NOT NULL,
		  `date` int(10) unsigned NOT NULL,
		  PRIMARY KEY  (`id_post`)
		);
EOD
		);
	}
	
	public function getPaginateList($start, $limit)
	{
		$users = new SloggyUsers();
		$sql = "
			SELECT
				posts.id,
				posts.title,
				posts.content,
				DATE_FORMAT(posts.date, '%d/%m/%Y') as postdate,
				users.login
			FROM {$this->getName()} posts
			JOIN {$users->getName()} users ON (users.id = posts.id_user)
			ORDER BY posts.date DESC
			LIMIT $start, $limit
		";
		return $this->fetchAll($sql);
	}
	
	public function getById($id)
	{
		$users = new SloggyUsers();
		
		$id = @intval($id);
		return $this->fetch("
			SELECT
				posts.id,
				posts.title,
				posts.content,
				DATE_FORMAT(posts.date, '%d/%m/%Y') as postdate,
				users.login as author
			FROM {$this->getName()} posts
			JOIN {$users->getName()} users ON (users.id = posts.id_user)
			WHERE posts.id = $id
		");
	}
}